package com.situ.edu.dao.impl;

import com.situ.edu.dao.IXiStudentDao;
import com.situ.edu.pojo.entity.Student;
import com.situ.edu.pojo.query.StudentQuery;
import com.situ.edu.pojo.vo.StudentVO;
import com.situ.edu.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.ObjectUtils;

import java.util.ArrayList;
import java.util.List;

public class XiStudentDaoImpl implements IXiStudentDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public List<StudentVO> selectByPage(StudentQuery stuQuery) {
        String sql = "SELECT s.id,s.name,s.age,s.gender,s.status,s.create_time,s.update_time,x.`name` AS xiName\n" +
                "from student AS s INNER JOIN xi AS x\n" +
                "ON s.xi_id = x.id where 1=1";

        List<Object> queryList = new ArrayList<>();
        String queryName = stuQuery.getName();
        if (queryName != null && !queryName.equals("")){
            sql += " and s.name like ?";
            queryList.add("%"+ queryName +"%");
        }

        if (!ObjectUtils.isEmpty(stuQuery.getXiId())){
            sql += " and xi_id=? ";
            queryList.add(stuQuery.getXiId());
        }

        sql += " limit ?,?";
        int offset = (stuQuery.getPage() - 1)*stuQuery.getLimit();
        queryList.add(offset);
        queryList.add(stuQuery.getLimit());
        List<StudentVO> list = template.query(sql,
                new BeanPropertyRowMapper<>(StudentVO.class),queryList.toArray());
        return list;
    }

    @Override
    public int selectTotalCount(StudentQuery stuQuery) {
        String sql = "SELECT count(*) \n" +
                "from student AS s INNER JOIN xi AS x\n" +
                "ON s.xi_id = x.id where 1=1";

        List<Object> queryList = new ArrayList<>();

        String queryName = stuQuery.getName();
        if (queryName != null && !queryName.equals("")){
            sql += " and s.name like ?";
            queryList.add("%"+ queryName +"%");
        }

        if (!ObjectUtils.isEmpty(stuQuery.getXiId())){
            sql += " and xi_id=? ";
            queryList.add(stuQuery.getXiId());
        }


        return template.queryForObject(sql, Integer.class, queryList.toArray());
    }

    @Override
    public void updateStatus(int id, int status) {
        template.update("update student set status=? where id=?",
                status,id);
    }

    @Override
    public void deleteById(int id) {
        template.update("delete from student where id=?",id);
    }

    @Override
    public void add(Student student) {
        template.update("insert into student(name,age,gender,xi_id) values (?,?,?,?)",
                student.getName(),student.getAge(),student.getGender(),student.getXiId());
    }

    @Override
    public Student selectById(int id) {
        return template.queryForObject("select * from student where id=?",
                new BeanPropertyRowMapper<>(Student.class),id);
    }

    @Override
    public void update(Student student) {
        template.update("update student set name=?,age=?,gender=? where xi_id=?",
                student.getName(),student.getAge(),student.getGender(),student.getXiId());
    }

    //教师管理：学生请假
    @Override
    public List<StudentVO> selectPage(StudentQuery stuQuery) {
        String sql = "SELECT s.id,te.name AS teacherName,s.name,s.age,s.gender,s.xi_id,s.`status`,c.name AS courseName,e.scores,c.gredit,x.id,x.name AS xiName\n" +
                "FROM `user` AS u INNER JOIN teacher AS te \n" +
                "ON u.id=te.user_id INNER JOIN taught AS ta\n" +
                "ON te.id=ta.teacher_id INNER JOIN course AS c\n" +
                "ON ta.course_id=c.id INNER JOIN elective AS e\n" +
                "ON c.id=e.course_id INNER JOIN student AS s\n" +
                "ON e.student_id=s.id INNER JOIN xi AS x\n" +
                "ON s.xi_id=x.id where 1=1";

        List<Object> queryList = new ArrayList<>();
        String queryName = stuQuery.getName();
        if (queryName != null && !queryName.equals("")){
            sql += " and s.name like ?";
            queryList.add("%"+ queryName +"%");
        }
        if (!ObjectUtils.isEmpty(stuQuery.getXiId())){
            sql += " and s.xi_id=? ";
            queryList.add(stuQuery.getXiId());
        }

        sql += " limit ?,?";
        int offset = (stuQuery.getPage() - 1)*stuQuery.getLimit();
        queryList.add(offset);
        queryList.add(stuQuery.getLimit());
        List<StudentVO> list = template.query(sql,
                new BeanPropertyRowMapper<>(StudentVO.class),queryList.toArray());
        return list;
    }

    @Override
    public int selectTotalCo(StudentQuery stuQuery) {
        String sql = "SELECT count(*)\n" +
                "FROM `user` AS u INNER JOIN teacher AS te \n" +
                "ON u.id=te.user_id INNER JOIN taught AS ta\n" +
                "ON te.id=ta.teacher_id INNER JOIN course AS c\n" +
                "ON ta.course_id=c.id INNER JOIN elective AS e\n" +
                "ON c.id=e.course_id INNER JOIN student AS s\n" +
                "ON e.student_id=s.id INNER JOIN xi AS x\n" +
                "ON s.xi_id=x.id where 1=1";

        List<Object> queryList = new ArrayList<>();

        String queryName = stuQuery.getName();
        if (queryName != null && !queryName.equals("")){
            sql += " and s.name like ?";
            queryList.add("%"+ queryName +"%");
        }

        if (!ObjectUtils.isEmpty(stuQuery.getXiId())){
            sql += " and s.xi_id=? ";
            queryList.add(stuQuery.getXiId());
        }

        return template.queryForObject(sql, Integer.class, queryList.toArray());
    }


}
